package edu.mju.dao.impl;

import edu.mju.dao.IScenicSpotDao;
import edu.mju.entity.ScenicSpot;
import edu.mju.util.Pager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author:wyp
 * @Date:2020/5/11 11:35
 */
public class ScenicSpotDaoImpl implements IScenicSpotDao {
    @Override
    public boolean insert(ScenicSpot scenicSpot) {
        boolean insert= false;
        try {
            Connection conn=MySqlDbUtil.getConnection();
            String sql = "INSERT INTO ScenicSpot(spotName,spotPlace,spotItem,spotPrice,spotOpenTime,spotImgUrl) VALUES (?,?,?,?,?,?)";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,scenicSpot.getSpotName());
            pstmt.setString(2,scenicSpot.getSpotPlace());
            pstmt.setString(3,scenicSpot.getSpotItem());
            pstmt.setInt(4,scenicSpot.getSpotPrice());
            pstmt.setString(5,scenicSpot.getSpotOpenTime());
            pstmt.setString(6,scenicSpot.getSpotImgUrl());
            int result =pstmt.executeUpdate();
            if(result>0){
                insert = true;
            }
            pstmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return insert;
    }

    @Override
    public boolean update(ScenicSpot scenicSpot) {
        boolean update = false;
        try {
            Connection conn=MySqlDbUtil.getConnection();
            String sql = "UPDATE ScenicSpot SET spotName=?,spotPlace=?,spotItem=?,spotPrice=?,spotOpenTime=?,spotImgUrl=? WHERE spotId=?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,scenicSpot.getSpotName());
            pstmt.setString(2,scenicSpot.getSpotPlace());
            pstmt.setString(3,scenicSpot.getSpotItem());
            pstmt.setInt(4,scenicSpot.getSpotPrice());
            pstmt.setString(5,scenicSpot.getSpotOpenTime());
            pstmt.setString(6,scenicSpot.getSpotImgUrl());
            pstmt.setInt(7,scenicSpot.getSpotId());

            int result =pstmt.executeUpdate();
            if(result>0){
                update = true;
            }
            pstmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return update;
    }


    @Override
    public boolean delete(Integer spotId) {
        boolean delete = false;
        try {
            Connection conn=MySqlDbUtil.getConnection();
            String sql = "DELETE FROM ScenicSpot WHERE spotId=?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,spotId);
            int result =pstmt.executeUpdate();
            if(result>0){
                delete = true;
            }
            pstmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return delete;
    }

    @Override
    public ScenicSpot findById(Integer id) {
        ScenicSpot scenicSpot = new ScenicSpot();
        try {
            Connection conn = MySqlDbUtil.getConnection();
            String sql = "SELECT * FROM ScenicSpot where spotId=?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            ResultSet rs = pstmt.executeQuery();
            if(rs.next()){
                scenicSpot.setSpotId(rs.getInt("spotId"));
                scenicSpot.setSpotName(rs.getString("spotName"));
                scenicSpot.setSpotPlace(rs.getString("spotPlace"));
                scenicSpot.setSpotItem(rs.getString("spotItem"));
                scenicSpot.setSpotPrice(rs.getInt("spotPrice"));
                scenicSpot.setSpotOpenTime(rs.getString("spotOpenTime"));
                scenicSpot.setSpotImgUrl(rs.getString("spotImgUrl"));
             }
            rs.close();
            pstmt.close();
            conn.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        return scenicSpot;
    }

    @Override
    public List<ScenicSpot> findAll() {
        List<ScenicSpot> listA = new ArrayList<>();
        try {
            Connection conn=MySqlDbUtil.getConnection();
            String sql = "SELECT * FROM ScenicSpot";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            ResultSet result = pstmt.executeQuery();
            while (result.next()){
                ScenicSpot scenicSpot = new ScenicSpot();
                scenicSpot.setSpotId(result.getInt("spotId"));
                scenicSpot.setSpotName(result.getString("spotName"));
                scenicSpot.setSpotPlace(result.getString("spotPlace"));
                scenicSpot.setSpotItem(result.getString("spotItem"));
                scenicSpot.setSpotPrice(result.getInt("spotPrice"));
                scenicSpot.setSpotOpenTime(result.getString("spotOpenTime"));
                scenicSpot.setSpotImgUrl(result.getString("spotImgUrl"));
                listA.add(scenicSpot);
            }
            result.close();
            pstmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return listA;
    }

    @Override
    public List<ScenicSpot> find(ScenicSpot user) {
        return null;
    }

    @Override
    public Pager pagenate(int page, int pageSize, ScenicSpot scenicSpot) {
        Pager pager = new Pager(page,pageSize);
        String sql = "select count(*) as total from ScenicSpot ";
        StringBuilder where = new StringBuilder("where 1=1 ");
        List<Object> params = new ArrayList<>();
        if(scenicSpot != null) {
            if (scenicSpot.getSpotName() != null && !"".equals(scenicSpot.getSpotName())) {
                where.append("AND spotName like ? ");
                params.add("%" + scenicSpot.getSpotName() + "%");
            }
        }
        try {
            Connection conn = MySqlDbUtil.getConnection();
            PreparedStatement pstmt = conn.prepareStatement(sql + where.toString());
            for(int i = 0;i<params.size();i++){
                pstmt.setObject(i+1,params.get(i));
            }
            ResultSet rs = pstmt.executeQuery();
            if(rs.next()){
                pager.setRows(rs.getInt("total"));
            }

            rs.close();;
            pstmt.close();
            //这里要注意sql 语句之间的空格
            sql = " select * from ScenicSpot ";
            pstmt = conn.prepareStatement(sql+where.toString()+" limit ?,? ");
            int index = 0;
            for(int i = 0;i<params.size();i++){
                ++index;
                pstmt.setObject(i+1,params.get(i));
            }
            pstmt.setObject(++index,(pager.getPage()-1)*pager.getPageSize());
            pstmt.setInt(++index,pager.getPageSize());
            rs =pstmt.executeQuery();
            List<Object> data = new ArrayList<>();
            while (rs.next()){
                scenicSpot = new ScenicSpot(rs.getInt("spotId"),rs.getString("spotName"),
                        rs.getString("spotPlace"),rs.getString("spotItem"),
                        rs.getInt("spotPrice"),rs.getString("spotOpenTime"),
                        rs.getString("spotImgUrl"));
                data.add(scenicSpot);
            }
            pager.setData(data);
            rs.close();
            pstmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return pager;
    }
}
